package bingo.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import net.bingosoft.common.ObjectRegistry;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


/**
 * JDBCHelper,封装常用sql操作
 * @author elvis
 */
final class JDBCHelper {

    private static final Log log = LogFactory.getLog(JDBCHelper.class);
    
    private static final Object[] EMPTY_PARAMS = new Object[]{};
    

    private JDBCHelper(){}
    
    /**
     * 获取一个默认的数据库连接
     * @return
     */
    public static Connection getConnection(){
    	try {
			return ObjectRegistry.getBaseDao().currentConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
    }
    
    /**
     * 执行一个SQL，不返回结果
     * @param sql
     */
    public static int executeUpdate(String sql) throws SQLException{
        Connection connection = getConnection();
        try {
            return executeUpdate(connection,sql);
        } finally  {
            closeConnection(connection);
        }
    }
    
    /**
     * 执行一个不带参数并且没有返回值的存储过程
     * @param procedureName
     * @throws SQLException
     */
    public static void executeProcedure(String procedureName) throws SQLException{
        Connection connection = getConnection();
        try {
            executeProcedure(connection,procedureName);
        } finally  {
            closeConnection(connection);
        }        
    }    
    
    /**
     * 执行一个SQL
     * @param sql
     * @param params
     * @throws SQLException
     */
    public static int executeUpdate(String sql , Object[] params) throws SQLException{
        Connection connection = getConnection();
        try {
            return executeUpdate(connection,sql,params);
        } finally  {
            closeConnection(connection);
        }        
    }
    
    /**
     * 批处理执行一个SQL，关于批处理，可参考PreparedStatement的addBatch方法
     * <p>
     * Examples:
     * <p>
     * <pre>
     * <code>
     *      String sql = "insert into user(id,name) values(?,?)";
     *      ArrayList paramsList = new ArrayList();
     *      paramsList.add(new Object[]{new Integer(1),"user1"});
     *      paramsList.add(new Object[]{new Integer(2),"user2"});
     *      paramsList.add(new Object[]{new Integer(3),"user3"});
     *      
     *      try{
     *          JDBCHelper.executeBatchUpdate(sql,paramsList);
     *      }catch(SQLException e){
     *          // handle the error
     *      }
     * </code>     
     * </pre>
     * @param sql
     * @param paramsList 参数值对象的List,参数值对象是一个Object[]类型
     * @return 
     * @throws SQLException
     */
    public static int[] executeBatchUpdate(String sql,List<?> paramsList) throws SQLException{
        Connection connection = getConnection();
        try {
            return executeBatchUpdate(connection,sql,paramsList);
        } finally  {
            closeConnection(connection);
        }
    }
    
    /**
     * 批处理执行一个SQL，关于批处理，可参考PreparedStatement的addBatch方法
     * <p>
     * Examples:
     * <p>
     * <pre>
     * <code>
     *      String sql = "insert into user(id,name) values(?,?)";
     *      ArrayList paramsList = new ArrayList();
     *      paramsList.add(new Object[]{new Integer(1),"user1"});
     *      paramsList.add(new Object[]{new Integer(2),"user2"});
     *      paramsList.add(new Object[]{new Integer(3),"user3"});
     *
     *      Connection connection = JDBCHelper.getConnection();
     *      try{
     *          JDBCHelper.executeBatchUpdate(connection,sql,paramsList);
     *      }catch(SQLException e){
     *          // handle the error
     *      }finally{
     *          JDBCHelper.closeConnection(connection);
     *      }
     * </code>     
     * </pre>
     * @param connection 
     * @param sql
     * @param paramsList 参数对象的List 参数对象是一个Object[]类型
     * @return 
     * @throws SQLException
     */    
    public static int[] executeBatchUpdate(Connection connection,String sql,List<?> paramsList) throws SQLException{
        int[] updateResult = new int[]{};
        
        PreparedStatement ps  = null;
        try{
            if(paramsList.size() > 0){
                ps = connection.prepareStatement(sql);
                for(int i=0;i<paramsList.size();i++){
                    Object[] parameters = (Object[])paramsList.get(i);
                    if(null != parameters){
                        for(int j = 0; j < parameters.length; j++){
                            ps.setObject(j + 1,parameters[j]);
                        }
                    }
                    ps.addBatch();
                    
                } 
                updateResult = ps.executeBatch();
            }
        }finally{
            closeStatement(ps);                
        }
        return updateResult;    
    }    
    
    /**
     * 执行一个不带参数并且没有返回值的存储过程
     * @param connection
     * @param procedureName
     * @throws SQLException
     */
    public static void executeProcedure(Connection connection,String procedureName) throws SQLException{
        String callProcecedureSQL = "{call " + procedureName + "()}"; 
        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(callProcecedureSQL);
            statement.execute();
        } finally{
            JDBCHelper.closeStatement(statement);
        }
    }
    
    /**
     * 执行一个只带输入参数并且没有返回值的存储过程
     * @param connection
     * @param procedureName
     * @throws SQLException
     */
    public static void executeProcedure(Connection connection,String procedureName,Object[] inputParams) throws SQLException{
        String callProcecedureSQL = prepareProcecure(procedureName,inputParams.length); 
        CallableStatement statement = null;
        try {
            statement = connection.prepareCall(callProcecedureSQL);
            for(int i=1;i<=inputParams.length;i++){
                statement.setObject(i,inputParams[i-1]);
            }
            statement.execute();
        } finally{
            JDBCHelper.closeStatement(statement);
        }
    }    
    
    /**
     * 执行一个只带输入参数并且没有返回值的存储过程
     * @param connection
     * @param procedureName
     * @throws SQLException
     */
    public static void executeProcedure(String procedureName,Object[] inputParams) throws SQLException{
        Connection connection = getConnection();
        try {
            executeProcedure(connection,procedureName,inputParams);
        } finally  {
            closeConnection(connection);
        }        
    }     
    
    /**
     * 执行一个不需要返回结果的sql语句
     * @param connection
     * @param sql sql语句
     * @throws SQLException if error occurs.
     * @return int update result.
     */
    public static int executeUpdate(Connection connection,String sql) throws SQLException{
        return executeUpdate(connection,sql,new Object[]{});
    }
    
    /**
     * 执行一个不需要返回结果的sql语句
     * @param connection 数据库连接 
     * @param sql sql语句
     * @param parameters 参数值
     * @throws SQLException if error occurs.
     * @return int update result.
     */
    public static int executeUpdate(Connection connection,String sql,Object[] parameters) throws SQLException{
        int updateResult = 0;
        
        PreparedStatement ps = null;
        try{
            ps = connection.prepareStatement(sql);
            if(null != parameters){
                for(int i = 0; i < parameters.length; i++){
                    ps.setObject(i + 1,parameters[i]);
                }
            }
            updateResult = ps.executeUpdate();
        }finally{
            closeStatement(ps);                
        }
        return updateResult;        
    }
    
    /**
     * 执行一个不需要返回结果的sql语句
     * @param connection 数据库连接 
     * @param sql sql语句
     * @param parameters 参数值
     * @throws SQLException if error occurs.
     * @return int update result.
     */
    public static int executeUpdate(Connection connection,String sql,List<?> parameters) throws SQLException{
        int updateResult = 0;
        
        PreparedStatement ps = null;
        try{
            ps = connection.prepareStatement(sql);
            if(null != parameters){
                for(int i = 0; i < parameters.size(); i++){
                    ps.setObject(i + 1,parameters.get(i));
                }
            }
            updateResult = ps.executeUpdate();
        }finally{
            closeStatement(ps);                
        }
        return updateResult;        
    }    
    
    /**
     * 查询sql结果，并把返回结果中的第一行，第一列的值以object类型返回
     * @param sql
     * @return 如果没有查找到记录返回null，否则返回第一行，第一列的值
     * @throws SQLException
     */
    public static Object queryObject(String sql) throws SQLException{
        Connection conn = getConnection();
        try{
            return queryObject(conn,sql);    
        }finally{
            closeConnection(conn);
        }
    }
    
    /**
     * 查询sql结果，并把返回结果中的第一行，第一列的值以object类型返回
     * @param sql
     * @return 如果没有查找到记录返回null，否则返回第一行，第一列的值
     * @throws SQLException
     */
    public static Object queryObject(Connection connection,String sql) throws SQLException{
        ResultSet rs = null;
        try{
            rs = executeQuery(connection,sql);
            if(rs.next()){
                return rs.getObject(1); 
            }
        }finally{
            closeResultSet(rs);
        }
        return null;
    }
    
    /**
     * 查询sql结果，并把返回结果中的第一行，第一列的值以object类型返回
     * @param sql
     * @return 如果没有查找到记录返回null，否则返回第一行，第一列的值
     * @throws SQLException
     */
    public static Object queryObject(Connection connection,String sql,Object[] params) throws SQLException{
        ResultSet rs = null;
        try{
            rs = executeQuery(connection,sql,params);
            if(rs.next()){
                return rs.getObject(1); 
            }
        }finally{
            closeResultSet(rs);
        }
        return null;
    }
        
    /**
     * 查询sql结果，并把返回结果中的第一行，第一列的值以object类型返回
     * @param sql
     * @param params
     * @return 如果没有查找到记录返回null，否则返回第一行，第一列的值
     * @throws SQLException
     */
    public static Object queryObject(String sql,Object[] params) throws SQLException{
        ResultSet rs = null;
        try{
            rs = executeQuery(sql,params);
            if(rs.next()){
                return rs.getObject(1); 
            }
        }finally{
            closeAll(rs);
        }
        return null;
    }
    
    /**
     * 执行一个Sql查询，使用默认的数据库连接
     * @param sql
     * @return
     * @throws SQLException
     */
    public static ResultSet executeQuery(String sql) throws SQLException{
    	ResultSet rs = null;
    	Connection connection = getConnection();
    	try{
    		rs =  executeQuery(connection,sql);
        	return rs;
    	}finally{
    		if(null == rs){
    			closeConnection(connection);
    		}
    	}    	
    }
    
    /**
     * 执行一个sql查询，使用默认的连接
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static ResultSet executeQuery(String sql,Object[] params) throws SQLException{
    	ResultSet rs = null;
    	Connection connection = getConnection();
    	try{
    		rs = executeQuery(connection,sql,params);
        	return rs;
    	}finally{
    		if(null == rs){
    			closeConnection(connection);
    		}
    	}
    }
    
    /**
     * 执行一个sql查询，并返回结果集
     * @param connection 数据库连接
     * @param sql sql语句
     * @throws SQLException if error occurs.
     * @return ResultSet
     */
    public static ResultSet executeQuery(Connection connection,String sql) 
        throws SQLException{
        return executeQuery(connection,sql,EMPTY_PARAMS);
    }
    
    /**
     * 执行一个sql查询，并返回结果集
     * @param connection
     * @param sql sql语句 
     * @param parameters 参数值
     * @throws SQLException if error occurs.
     * @return ResultSet
     */
    public static ResultSet executeQuery(Connection connection,String sql,Object[] parameters) throws SQLException{
        ResultSet rs = null;
        PreparedStatement ps = null;
        ps = connection.prepareStatement(sql);
        
        log.debug(sql);
        
        if(null != parameters){
            for(int i = 0; i < parameters.length; i++){
                ps.setObject(i + 1,parameters[i]);
                if(log.isDebugEnabled()){
                    log.debug("set parameter " + i + " to:" + parameters[i].toString());
                }                
            }
        }
        rs = ps.executeQuery();            
        return rs;
    }

    /**
     * 执行一个sql查询，并返回结果集
     * @param connection
     * @param sql sql语句 
     * @param parameters 参数值
     * @throws SQLException if error occurs.
     * @return ResultSet
     */
    public static ResultSet executeQuery(Connection connection,String sql,List<?> parameters) throws SQLException{
        ResultSet rs = null;
        PreparedStatement ps = null;
        
        log.debug(sql);

        ps = connection.prepareStatement(sql);
        if(null != parameters){
            for(int i = 0; i < parameters.size(); i++){
                ps.setObject(i+1,parameters.get(i));
                if(log.isDebugEnabled()){
                    log.debug("set parameter " + i + " to:" + parameters.get(i).toString());
                }
            }
        }
        rs = ps.executeQuery();         
        return rs;
    }
    

    
    
    /**
     * 关闭一个JDBC数据库连接 
     * @param connection 数据库连接
     */
    public static void closeConnection(Connection connection){
        if(null != connection){
            try{
                connection.close();
            }catch(Exception e){
                log.warn(e);
            }
        }
    }
    
    /**
     * 关闭打开的ResultSet
     * @param rs
     */
    public static void closeResultSet(ResultSet rs){
        if(null != rs){
            try{
                Statement statement = rs.getStatement();
                closeStatement(statement);                
            }catch(Throwable e){
                log.error(e);
            }finally{
                try {
                    rs.close();
                } catch (SQLException e) {}
            }
        }
    }
    
    /**
     * 关闭打开的resultset及reresuletset绑定的statement,connection
     * @param rs
     */
    public static void closeAll(ResultSet rs){
        if(null != rs){
            Statement statement = null;
            try {
                statement = rs.getStatement();
                rs.close();
                
            } catch (Throwable e) {
                
            } finally{
                closeAll(statement);
            }
        }
    }
    
    /**
     * 只关闭打开的resultset和resultset绑定的statement，不关闭connection
     * @param rs
     */
    public static void closeStatement(ResultSet rs){
        if(null != rs){
            Statement statement = null;
            try {
                statement = rs.getStatement();
                rs.close();
                
            } catch (Throwable e) {
                
            } finally{
                closeStatement(statement);
            }
        }        
    }
    
    /**
     * 关闭打开的statement及statement绑定的connection
     * @param statement
     */
    public static void closeAll(Statement statement){
        if(null != statement){
            Connection connection = null;
            try {
                connection = statement.getConnection();
                statement.close();
            } catch (Throwable e) {
                
            } finally{
                closeConnection(connection);
            }
        }
    }
    
    /**
     * 关闭打开的Statement
     * @param ps
     */
    public static void closeStatement(Statement statement){
        if(null != statement){
            try{
                statement.close();
            }catch(Throwable e){
                log.warn(e);
            }
        }        
    }    

    /**
     * 
     * @param connection
     */
    public static void rollBack(Connection connection){
        if(null != connection){
            try {
                connection.rollback();
            } catch (SQLException e) {
                log.warn(e);
            }
        }
    }
    
    private static String prepareProcecure(String procedureName,int paramSize){
        String statment = "{ call " + procedureName + "(";
        int count = paramSize;
        for(int i=0;i<count;i++){
            statment += "?,";
        }           
        
        statment = statment.substring(0,statment.length() -1);
        statment += ") }";
        
        return statment;
    }    
}